﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Settings
{
    public class NotebookImport
    {

        // Address Book
        public void ImportAddressBook(string ExcelFilePath)
        {
            string sSqlTable = "[SYNC_ADDRESSBOOK]";
            string MyExcelDataQuery = "SELECT BPCode, BPName, BPType, UnitNo, Street, Brgy, CityOrMunicipal, PostalCode, ProvinceOrMetro, Address, Phone, Fax, ContactPerson, TIN, PaymentTerm, CreditLimit, PriceList, SMCODE, AreaCode, Teritorry, CustomerGroup, BPCodeParent, Institution, StoreCode, AcctCode, VATCODE, WithHoldingTax, Division, Disc1, Disc2, Disc3, Disc4, Documnt FROM [ADDRESSBOOK$]";
            
            try
            {
                //create our connection strings
                string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES\"";
                string sConnectionString = "Data Source=sqlsvr;Initial Catalog=DB_AF;User Id=sa;Password=nidaros;";
                string sClearSql = "DELETE FROM " + sSqlTable;


                SqlConnection oConnection = new SqlConnection(sConnectionString);
                SqlCommand oCommand = new SqlCommand(sClearSql, oConnection);
                oConnection.Open();
                oCommand.ExecuteNonQuery();
                oConnection.Close();

                //series of commands to bulk copy data from the excel file into our sql table
                OleDbConnection oledbconn = new OleDbConnection(sExcelConnectionString);
                OleDbCommand oledbcmd = new OleDbCommand(MyExcelDataQuery, oledbconn);
                oledbconn.Open();
                OleDbDataReader dr = oledbcmd.ExecuteReader();
                
                SqlBulkCopy bulkcopy = new SqlBulkCopy(sConnectionString);
                bulkcopy.DestinationTableName = sSqlTable;
                
                while (dr.Read())
                {
                    bulkcopy.WriteToServer(dr);
                }
     
                oledbconn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
                //handle exception
            }
        }

        #region Price List
        public void ImportPricelistHeader(string ExcelFilePath)
        {

        }
        public void ImportPriceListDetails(string ExcelFilePath)
        {
        }
        #endregion

        #region Sales Order
        public void ImportSalesOrderHeader(string ExcelFilePath)
        {
        }
        public void ImportSalesOrderDetail(string ExcelFilePath)
        {
        }
        #endregion

        #region Purchase Order
        public void ImportPurchaseOrderHeader(string ExcelFilePath)
        {

        }
        public void ImportPurchaseOrderDetail(string ExcelFilePath)
        {
        }
        #endregion

    }
}
